/*
 * Creato da SharpDevelop.
 * Utente: nbasile
 * Data: 04/02/2009
 * Ora: 10.46
 * 
 * To change this template use Tools | Options | Coding | Edit Standard Headers.
 */

using System;
using Mono.Data.SqliteClient;
using System.Collections;

namespace webpman
{
	/// <summary>
	/// Description of DBManager.
	/// </summary>
	public class DBManager
	{
		
			
		
	
		// Local Objects
		private SqliteConnection sqlconn;
		private SqliteCommand sqlcommand = null;
		private SqliteDataReader sqlreader;
		private SqliteTransaction sqltrans;
		
		
		//private SQLiteConnection sqlconn;
		//private SQLiteCommand sqlcommand = null;
		//private SQLiteDataReader sqlreader;
		//private SQLiteTransaction sqltrans;

		private string connString = "";
		private System.Collections.Specialized.NameValueCollection SelectString = 
			new System.Collections.Specialized.NameValueCollection();
		private string sqlText = "";
		
		
		
		public DBManager(string dbpath)
		{
			
			#region RSS_PRODUCT
			
				SelectString["RSS_PRODUCT"] = 
					"SELECT " + 
						"T_ITEM.T_ITEM_ID AS ID, " +
						"T_ITEM.T_ITEM_NAME AS NAME, " +
						"strftime(\"%Y-%m-%d %H:%M\", T_ITEM.T_ITEM_INSDATE) AS DATEINS, " +
						"T_USER.T_USER_ACCOUNT AS USER, " +
						"T_TASK.T_TASK_NAME AS TASK, " +
						"T_STATE.T_STATE_NAME AS STATE,  " +
						"T_ITEM.T_ITEM_DESCRIPTION AS DESCRIPTION, " +
						"T_PROJECT.T_PROJECT_NAME AS PROJECT " +
					"FROM " +
						"T_TASK, " +
						"T_USER, " +
						"T_ITEM, " +
						"T_PROJECT, " +
						"T_STATE " +
					"WHERE " +
						"T_ITEM.T_TASK_ID = T_TASK.T_TASK_ID AND " +
						"T_ITEM.T_STATE_ID = T_STATE.T_STATE_ID AND " +
						"T_PROJECT.T_PROJECT_ID = T_TASK.T_PROJECT_ID AND " +
						"T_ITEM.T_USER_ID = T_USER.T_USER_ID AND " +
						"T_PROJECT.T_PROJECT_ID = ";
			
	 	    #endregion RSS_PRODUCT

			
			#region RSS_TASK
			
				SelectString["RSS_TASK"] = 
					"SELECT " + 
						"T_ITEM.T_ITEM_ID AS ID, " +
						"T_ITEM.T_ITEM_NAME AS NAME, " +
						"strftime(\"%Y-%m-%d %H:%M\", T_ITEM.T_ITEM_INSDATE) AS DATEINS, " +
						"T_USER.T_USER_ACCOUNT AS USER, " +
						"T_TASK.T_TASK_NAME AS TASK, " +
						"T_STATE.T_STATE_NAME AS STATE,  " +
						"T_ITEM.T_ITEM_DESCRIPTION AS DESCRIPTION " +
					"FROM " +
						"T_TASK, " +
						"T_USER, " +
						"T_ITEM, " +
						"T_STATE " +
					"WHERE " +
						"T_ITEM.T_TASK_ID = T_TASK.T_TASK_ID AND " +
						"T_ITEM.T_STATE_ID = T_STATE.T_STATE_ID AND " +
						"T_ITEM.T_USER_ID = T_USER.T_USER_ID AND " +
						"T_TASK.T_TASK_ID = ";
			
	 	    #endregion RSS_TASK

			
			try
			{
				// Connection String for SQLite DB
				connString = "URI=file:" + dbpath + ",Version=3";
				
				// Debug
				// ABasile.Utility Uman = new ABasile.Utility();
				// Uman.AppendTraceFile("DBManager.cs", "DBManager", connString);
				
				// Connecting database
				sqlconn = new SqliteConnection(connString);

				// Open the connection
				sqlconn.Open();
				

			}
			catch (System.Security.SecurityException Ex)
			{
				throw Ex;
			}
			catch(Exception Ex)
			{
				throw Ex;
			}

		}
		
		
		public void Close()
		{
			if (sqlconn.State != System.Data.ConnectionState.Closed)
			{	
				sqlconn.Close();
				sqlconn.Dispose();
				sqlconn = null;
			}
		}
				
		
		
		public string CheckUser(string Account, string Password)
		{
			// Verify user account, password and retrieve authorization
			sqlText = "SELECT * FROM T_USER " +
				      "WHERE T_USER_ACCOUNT = '" + Account.Replace("'","''") + "' AND " +
					  "T_USER_PASSWORD = '" + Password.Replace("'","''") + "'";
			
			PrepareCommand(sqlText, false);
			sqlreader = sqlcommand.ExecuteReader();

			if (sqlreader.Read() == false)
			{
				if (!sqlreader.IsClosed)
					sqlreader.Close();
				return "";
			}

			string Ret = ((string)sqlreader["T_USER_TYPE"]);
			if (!sqlreader.IsClosed)
				sqlreader.Close();
			return Ret;
			
		}
		
		

		public string CheckAuthProject(string Account, string objectType, string objectID)
		{
			// Get user account authorization
			sqlText = "SELECT " +
						"T_PROJECT.T_PROJECT_ID, " +
						"T_PROJECT.T_PROJECT_NAME, " + 
						"T_TASK.T_TASK_ID, " +
						"T_TASK.T_TASK_NAME, " +
						"T_ITEM.T_ITEM_ID, " +
						"T_ITEM.T_ITEM_NAME, " +
						"T_USER.T_USER_ACCOUNT, " +
						"T_AUTH.T_AUTH_ID, " +
						"T_AUTH.T_AUTH_TYPE " +
					"FROM " +
						"T_AUTH, " +
						"T_USER, " +
						"T_PROJECT " +
						"	LEFT OUTER JOIN T_TASK ON T_PROJECT.T_PROJECT_ID = T_TASK.T_PROJECT_ID " +
						"		LEFT OUTER JOIN T_ITEM ON T_TASK.T_TASK_ID = T_ITEM.T_TASK_ID " +
					"WHERE " +
						"T_USER.T_USER_ID = T_AUTH.T_USER_ID AND " +
						"T_PROJECT.T_PROJECT_ID = T_AUTH.T_PROJECT_ID AND " +
						"T_USER.T_USER_ACCOUNT = '" + Account + "' AND ";			
			
			
			if (objectType == "project")
				sqlText += "T_PROJECT.T_PROJECT_ID = " + objectID;
			else if (objectType == "task")
				sqlText += "T_TASK.T_TASK_ID = " + objectID;
			else if (objectType == "item")
				sqlText += "T_ITEM.T_ITEM_ID = " + objectID;

			// Debug
			// ABasile.Utility Uman = new ABasile.Utility();
			// Uman.AppendTraceFile("DBManager.cs", "CheckAuthProject", sqlText);
			
			PrepareCommand(sqlText, false);
			sqlreader = sqlcommand.ExecuteReader();

			if (sqlreader.Read() == false)
			{
				if (!sqlreader.IsClosed)
					sqlreader.Close();
				return "";
			}

			string Ret = ((string)sqlreader["T_AUTH_TYPE"]);
			if (!sqlreader.IsClosed)
				sqlreader.Close();
			return Ret;
			
		}
		
		
		
		public ArrayList GetRecordset(string sqlTextCommand)
		{
			ArrayList RetData = new ArrayList();
			
			PrepareCommand(sqlTextCommand, false);			
			sqlreader = sqlcommand.ExecuteReader();

			string[] TableHead = new string[sqlreader.FieldCount];
			for (int l=0; l<TableHead.Length; l++)
				TableHead[l] = sqlreader.GetName(l);
			
			RetData.Add(TableHead);
			
			while (sqlreader.Read())
			{
				
				object[] values = new object[sqlreader.FieldCount];
   				sqlreader.GetValues(values);
   				RetData.Add(values);
			}
			
			if (!sqlreader.IsClosed)
				sqlreader.Close(); sqlreader.Dispose(); sqlreader = null;
			
			return RetData;
		}


		public string GetRssId(string USERNAME)
		{
			System.Collections.ArrayList AObj = GetRecordset(
				"SELECT T_USER_RSSID FROM T_USER WHERE T_USER_ACCOUNT = '" + USERNAME + "'");
			
			if (AObj.Count > 1)
			{
				object[] outobj = (object[])AObj[1]; 
				return Convert.ToString(outobj[0]);
			}
			else
				return "";
		}
		
		
		public string CheckRssId(string RSSID)
		{
			System.Collections.ArrayList AObj = GetRecordset(
				"SELECT T_USER_ACCOUNT FROM T_USER WHERE T_USER_RSSID = '" + RSSID + "'");
			
			if (AObj.Count > 1)
			{
				object[] outobj = (object[])AObj[1]; 
				return Convert.ToString(outobj[0]);
			}
			else
				return "";
		}
		
		
		public string ExecSQL(string sqlTextCommand)
		{
			PrepareCommand(sqlTextCommand, true);
			try
			{
				sqlcommand.ExecuteNonQuery();
				sqltrans.Commit();
				return "";
			}
			catch (Exception Ex)
			{
				sqltrans.Rollback();				
				return Ex.Message;
			}
		}
		
		
		
		public ArrayList GetTableData(string TName, string Rid)
		{
			string sqlTextCommand = "";			
			ArrayList RetTab = new ArrayList();
			System.Collections.Generic.List<string> RetString = new System.Collections.Generic.List<string>();
			System.Collections.Generic.List<string> RetNameString = new System.Collections.Generic.List<string>();
			
			
			#region Prepare Select Command
			
			  #region T_USER
			  
			  	SelectString["T_USER"] = "SELECT " +
			    		"T_USER_ID, " +
			    		"T_USER_ACCOUNT, " +
			    		"T_USER_REALNAME, " +
			    		"T_USER_DESCRIPTION, " +
			    		"T_USER_GROUP, " +
			    		"T_USER_PASSWORD, " +
			    		"T_USER_TYPE, " +
			    		"T_USER_ACTIVE " +
			    	"FROM T_USER ";
			
			  #endregion T_USER
			
			  #region T_PROJECT
			
			  SelectString["T_PROJECT"] = "SELECT " +
			    			"T_PROJECT_ID, " +
			    		    "T_PROJECT_NAME, " +
			    			"T_PROJECT_DESCRIPTION, " +
			    			"T_PROJECT_STARTDATE, "	+					    
						    "T_PROJECT_ESTENDDATE, " +
						    "T_PROJECT_ENDDATE, " +
			    			"T_PROJECT_ACTIVE, " +
			    			"T_USER_ID, " +
						    "T_STATE_ID, " +
							"T_CATEGORY_ID, " +
						    "T_PROJECT_NOTES "+					    
			    	"FROM T_PROJECT ";
			
			  #endregion T_PROJECT

			  #region T_TASK
			
				SelectString["T_TASK"] = "SELECT " +
						"T_TASK_ID, " +
						"T_TASK_NAME, " +
					    "T_TASK_DESCRIPTION, " +
						"T_TASK_STARTDATE, " +
						"T_TASK_ENDDATE, " +
					    "T_TASK_ESTENDDATE, " +						
						"T_TASK_ACTIVE, " +
						"T_TASK_COMPLETE, " +
						"T_USER_ID, " +
						"T_STATE_ID, " +
					    "T_CATEGORY_ID, " +
					    "T_PROJECT_ID, " +
						"T_TASK_WEIGHT, " +
					    "T_TASK_NOTES " +
					"FROM T_TASK";
			
			  #endregion T_TASK
			
			  #region T_ITEM
			
				SelectString["T_ITEM"] = "SELECT " +
					    "T_ITEM_ID, " +
						"T_ITEM_NAME, " +
						"T_ITEM_DESCRIPTION, " +				    
					    "T_ITEM_INSDATE, " +				    
					    "T_ITEM_COMPLETE, " +
					    "T_STATE_ID, " +
					    "T_USER_ID, " +
					    "T_TASK_ID " +
					"FROM T_ITEM ";
			
			  #endregion T_ITEM
			
			  #region T_AUTH
			
				SelectString["T_AUTH"] = "SELECT " +
					    "T_AUTH_ID, " +					
					    "T_PROJECT_ID, " +
					    "T_USER_ID, " +
					    "T_AUTH_TYPE " +
					"FROM T_AUTH";
			
			  #endregion T_AUTH
			
			  #region T_STATE
			
				SelectString["T_STATE"] = "SELECT " +
						"T_STATE_ID, " + 
						"T_STATE_NAME, " + 
						"T_STATE_APPLYTO " + 
					"FROM T_STATE";
			
			  #endregion T_STATE
			
			  #region T_CATEGORY
			
				SelectString["T_CATEGORY"] = "SELECT " +
						"T_CATEGORY_ID, " + 
						"T_CATEGORY_NAME, " + 
						"T_CATEGORY_APPLYTO " + 
					"FROM T_CATEGORY";
			
			  #endregion T_CATEGORY
			

					
			#endregion Prepare Select Command
			
			if (Rid == "")
			{
				// Only Fields Name
				sqlTextCommand = SelectString[TName] + " LIMIT 1";
				PrepareCommand(sqlTextCommand, false);
				sqlreader = sqlcommand.ExecuteReader();
				
				for (int h=0; h<sqlreader.FieldCount; h++)
					RetString.Add(Convert.ToString(sqlreader.GetName(h)));
								
				if (!sqlreader.IsClosed)
					sqlreader.Close(); sqlreader.Dispose(); sqlreader = null;
				
				RetTab.Add(RetString.ToArray() );
				
			}
			else
			{
				// Fields Name and record values
				sqlTextCommand = SelectString[TName] + " WHERE " + TName + "_ID = " + Rid;
				PrepareCommand(sqlTextCommand, false);
				sqlreader = sqlcommand.ExecuteReader();
				if (sqlreader.Read())
				{
					for (int h=0; h<sqlreader.FieldCount; h++)
					{
						RetString.Add(Convert.ToString(sqlreader[h] ));
						RetNameString.Add(Convert.ToString(sqlreader.GetName(h)));
					}
				}
				
				if (!sqlreader.IsClosed)
					sqlreader.Close(); sqlreader.Dispose(); sqlreader = null;
				
				RetTab.Add(RetNameString.ToArray());
				RetTab.Add(RetString.ToArray());
				
				
			}
			
			return RetTab;
		}
		
		
		
		public string GetProjectCompleted(string projectname)
		{
			sqlText = "";
			PrepareCommand(sqlText, false);
			sqlreader = sqlcommand.ExecuteReader();
			
			return "";
		}



		private void PrepareCommand(string commandText, bool withTransaction)
		{
			sqlcommand = new SqliteCommand();
 			sqlcommand.Connection = sqlconn;
			
			if (withTransaction == true)
			{
				sqltrans = (SqliteTransaction)sqlconn.BeginTransaction();
 				sqlcommand.Transaction = sqltrans;
			}
			
 			sqlcommand.CommandText = commandText;
		}


		public System.Data.IDataReader GetRssData(int objectID, string objectName)
		{
			string sqlTextCommand = "";
			
			if (objectName == "project")
				sqlTextCommand = SelectString["RSS_PRODUCT"] + objectID.ToString();
			else if (objectName == "task")
				sqlTextCommand = SelectString["RSS_TASK"] + objectID.ToString();
			else
				sqlTextCommand = SelectString["RSS_PRODUCT"] + objectID.ToString();
			
			PrepareCommand(sqlTextCommand, false);
			return sqlcommand.ExecuteReader();
		}
		
		
	}
}
